import json

import pandas as pd

import mysql_util


def find_farm_id(orgCode, farmOrg):
    sql = "SELECT o1.CODE AS org_code, o1.id AS org_id, o2.CODE AS farm_org, o2.id AS farm_id, o1.flag, o1.business_id, o2.project FROM pc_organization o1 INNER JOIN pc_organization o2 ON o2.parent_id = o1.id WHERE 1 = 1 AND o1.CODE = '" \
          + orgCode + "' AND o2.LEVEL = '5' AND o2.CODE = '" \
          + farmOrg + "'"
    rs = mysql_util.select_by_sql(sql)
    # print(rs)
    # print(type(rs))
    rs = json.loads(rs)
    # print(type(rs))
    if len(rs) > 0:
        info = rs[0]
        # print(info['farm_id'])
        return info['farm_id']
    return None


def find_org_id(orgCode):
    sql = "SELECT o1.CODE AS org_code, o1.id AS org_id, o1.flag, o1.business_id FROM pc_organization o1 WHERE 1 = 1 AND o1.CODE = '" + orgCode + "' and o1.level = '4'"
    rs = mysql_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(type(rs))
    if len(rs) > 0:
        info = rs[0]
        # print(info['farm_id'])
        return info['org_id']
    return None


if __name__ == '__main__':
    # 读取excel
    file = "C:/Users/yangjianzhang/Desktop/test.xlsx"

    data = pd.read_excel(file)  # reading file
    print(type(data))
    # print(data)
    for index, row in data.iterrows():
        # print(row['org_code'], row['farmOrg'])
        user_id = '1024178'
        # 查询场id
        org_id = find_org_id(row['org_code'])
        exclude = ['108915', '108924', '110849', '111378', '1737582']
        if str(org_id) not in exclude:
            sql = "INSERT INTO pc_user_organization ( business_id, LEVEL, organization_id, user_id, create_user ) " \
                  "VALUES ( '10', '5', '" + str(org_id) + "', '" + str(user_id) + "', '111111' );"
            print(sql)
